
package capa_persistencia;

//Esta clase crea las consultas que seran usadas por AccesoBD
public class Consultas 
{
    public String crearBD()
    {
        String consulta = "CREATE DATABASE PartidasGuardadas";
        return consulta;
    }
    
    public String crearTablaEscenarios()
    {
        String consulta = "CREATE TABLE Escenarios( \n" +
        "IdEscenario INT NOT NULL, \n" +
        "Nombre VARCHAR(50) NOT NULL, \n" +
        "PRIMARY KEY (IdEscenario))";
        return consulta;
    }
    
    public String crearTablaPartidas()
    {
        String consulta = "CREATE TABLE Partidas( \n" +
        "IdPartida INT NOT NULL, \n" +
        "IdEscenario INT NOT NULL, \n" +
        "Minutos INT NOT NULL, \n" + 
        "Segundos INT NOT NULL, \n" +
        "PRIMARY KEY (IdPartida),\n" +
        "FOREIGN KEY (IdEscenario) REFERENCES Escenarios(IdEscenario))";
        return consulta;
    }
    
    public String crearTablaActores()
    {
        String consulta = "CREATE TABLE Actores( \n" +
        "IdActor INT NOT NULL, \n" +
        "PosicionX INT NOT NULL, \n" +
        "PosicionY INT NOT NULL, \n" +
        "Energia INT NOT NULL, \n" +
        "IdPartida INT NOT NULL, \n" +
        "Orientacion INT NOT NULL, \n" +
        "PRIMARY KEY (IdActor),\n" +
        "FOREIGN KEY (IdPartida) REFERENCES Partidas(IdPartida))";
        return consulta;
    }
    
    public String crearTablaPilotos()
    {
        String consulta = "CREATE TABLE Pilotos( \n" +
        "IdActor INT NOT NULL, \n" +
        "LlamoDrone BIT NOT NULL, \n" +
        "FOREIGN KEY (IdActor) REFERENCES Actores(IdActor))";
        return consulta;
    }
    
    public String crearTablaAviones()
    {
        String consulta = "CREATE TABLE Aviones( \n" +
        "IdActor INT NOT NULL, \n" +
        "Balas INT NOT NULL, \n" +
        "Bombas INT NOT NULL, \n" +
        "FOREIGN KEY (IdActor) REFERENCES Actores(IdActor))";
        return consulta;
    }
    
    public String crearTablaTanques()
    {
        String consulta = "CREATE TABLE Tanques( \n" +
        "IdActor INT NOT NULL, \n" +
        "Municion INT NOT NULL, \n" +
        "FOREIGN KEY (IdActor) REFERENCES Actores(IdActor))";
        return consulta;
    }
    
    public String crearTablaDrones()
    {
        String consulta = "CREATE TABLE Drones( \n" +
        "IdActor INT NOT NULL, \n" +
        "FOREIGN KEY (IdActor) REFERENCES Actores(IdActor))";
        return consulta;
    }
    
    public String crearTablaObstaculos()
    {
        String consulta = "CREATE TABLE Obstaculos( \n" +
        "Clave INT NOT NULL, \n" +
        "IdEscenario INT NOT NULL, \n" +
        "PosicionX INT NOT NULL, \n" +
        "PosicionY INT NOT NULL, \n" +
        "Tipo CHAR NOT NULL, \n" +
        "Base INT NOT NULL, \n" +
        "Altura INT NOT NULL, \n" +
        "PRIMARY KEY (Clave), \n" +
        "FOREIGN KEY (IdEscenario) REFERENCES Escenarios(IdEscenario))";
        return consulta;
    }
    
    public String insertarEscenario()
    {
        String consulta = "INSERT INTO Escenarios \n" +
        "(IdEscenario, Nombre) VALUES (?, ?)";
        return consulta;
    }
    
    public String insertarPartida()
    {
        String consulta = "INSERT INTO Partidas \n" +
        "(IdPartida, IdEscenario, Minutos, Segundos) VALUES (?, ?, ?, ?)";
        return consulta;
    }
    
    public String insertarActor()
    {
        String consulta = "INSERT INTO Actores \n" +
        "(IdActor, PosicionX, PosicionY, Energia, IdPartida, Orientacion) VALUES (?, ?, ?, ?, ?, ?)";
        return consulta;
    }
    
    public String insertarPiloto()
    {
        String consulta = "INSERT INTO Pilotos \n" +
        "(IdActor, LlamoDrone) VALUES (?, ?)";
        return consulta;
    }
    
    public String insertarAvion()
    {
        String consulta = "INSERT INTO Aviones \n" +
        "(IdActor, Balas, Bombas) VALUES (?, ?, ?)";
        return consulta;
    }
    
    public String insertarTanque()
    {
        String consulta = "INSERT INTO Tanques \n" +
        "(IdActor, Municion) VALUES (?, ?)";
        return consulta;
    }
    
    public String insertarDrone()
    {
        String consulta = "INSERT INTO Drones \n" +
        "(IdActor) VALUES (?)";
        return consulta;
    }
    
    public String insertarObstaculo()
    {
        String consulta = "INSERT INTO Obstaculos \n" +
        "(Clave, IdEscenario, PosicionX, PosicionY, Tipo, Base, Altura) VALUES (?, ?, ?, ?, ?, ?, ?)";
        return consulta;
    }
    
    public String cargarObstaculos()
    {
        String consulta = "SELECT * FROM Obstaculos \n" +
        "WHERE IdEscenario = ?";
        return consulta;
    }
    
    public String cargarObstaculo()
    {
        String consulta = "SELECT * FROM Obstaculos \n" +
        "WHERE Clave = ?";
        return consulta;
    }
    
    public String cargarPartida()
    {
        String consulta = "SELECT * FROM Partidas \n" +
        "WHERE IdPartida = ?";
        return consulta;
    }
    
    public String cargarTiempoPartida()
    {
        String consulta = "SELECT p.Minutos, p.Segundos FROM Partidas p \n" +
        "WHERE IdPartida = ?";
        return consulta;
    }
    
    public String cargarEscenario()
    {
        String consulta = "SELECT * FROM Escenarios \n" +
        "WHERE IdEscenario = ?";
        return consulta;
    }
    
    public String cargarActor()
    {
        String consulta = "SELECT * FROM Actores \n" +
        "WHERE IdPartida = ?";
        return consulta;
    }
    
    public String cargarPiloto()
    {
        String consulta = "SELECT * FROM Actores A, Pilotos P \n" +
        "WHERE A.IdActor = P.IdActor AND A.IdPartida = ?";
        return consulta;
    }
    
    public String cargarTanques()
    {
        String consulta = "SELECT * FROM Actores A, Tanques T \n" +
        "WHERE A.IdActor = T.IdActor AND A.IdPartida = ?";
        return consulta;
    }
    
    public String cargarDrone()
    {
        String consulta = "SELECT * FROM Actores A, Drones D \n" +
        "WHERE A.IdActor = D.IdActor AND A.IdPartida =?";
        return consulta;
    }
    
    public String cargarAvion()
    {
        String consulta = "SELECT * FROM Actores A, Aviones V \n" +
        "WHERE A.IdActor = V.IdActor AND A.IdPartida = ?";
        return consulta;
    }
    
    public String contarPartidas()
    {
        String consulta = "SELECT IdPartida FROM Partidas p \n" + 
        "WHERE p.IdPartida = (SELECT MAX(IdPartida) FROM Partidas)";
        return consulta;
    }
    
    public String contarActores()
    {
        String consulta = "SELECT IdActor FROM Actores a \n" + 
        "WHERE a.IdActor = (SELECT MAX(IdActor) FROM Actores)";
        return consulta;
    }
    
    public String contarObstaculos()
    {
        String consulta = "SELECT Clave FROM Obstaculos o \n" + 
        "WHERE o.Clave = (SELECT MAX(Clave) FROM Obstaculos)";
        return consulta;
    }
}
